﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BusinessLibrary.BLL;
using System.Data.SqlClient;

namespace BusinessLibrary.DAL
{
    class sqlNiveau
    {
        private static Niveau ReaderToObject(SqlDataReader dr)
        {
            Niveau _niveau = new Niveau();
            _niveau.Id = int.Parse(dr["Id"].ToString());

            _niveau.Nom = dr["Nom"].ToString();
            return _niveau;

        }

        public static Niveau GetNiveauById(int id)
        {
            Niveau niveau = new Niveau();
            string strSQL = @"select * from Niveau where UserId=@Id";
            try
            {
                using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString))
                {
                    cnx.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cnx);
                    cmd.Parameters.AddWithValue("@Id", id);
                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        niveau = ReaderToObject(dr);
                    }
                    dr.Close();
                    cnx.Close();

                }
            }
            catch (Exception) { }
            return niveau;
        }

        public static int insertNiveau(Niveau n)
        {
            int x = 0;
            string strSQL = @"insert into Niveau(Nom) values(@nom)";
            try
            {
                using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString))
                {
                    cnx.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cnx);

                    cmd.Parameters.AddWithValue("@nom", n.Nom);

                    x = cmd.ExecuteNonQuery();
                    cnx.Close();
                }
            }
            catch (Exception) { }
            return x;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="s"></param>
        /// <returns></returns>
        public static int updateNiveau(Niveau n)
        {
            int x = 0;

            string strSQl = @"UPDATE Niveau SET (@nom IS NULL OR Nom=@nom) where Id=@id ";
            try
            {
                using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString))
                {
                    cnx.Open();

                    SqlCommand cmd = new SqlCommand(strSQl, cnx);

                    cmd.Parameters.AddWithValue("@nom", n.Nom);

                    x = cmd.ExecuteNonQuery();

                    cnx.Close();
                }
            }
            catch (Exception) { }
            return x;
        }



        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <param name="idAuthentif"></param>
        /// <returns></returns>
        public static int deletNiveau(int id)
        {
            int x = 0;
            string strSQL = @"delete from Niveau where id=@id)";

            try
            {
                using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString))
                {
                    cnx.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cnx);

                    cmd.Parameters.AddWithValue("@id", id);

                    x = cmd.ExecuteNonQuery();

                    cnx.Close();
                }
            }
            catch (Exception) { }
            return x;
        }


        /// <summary>
        /// retourner une liste des niveau
        /// </summary>
        /// <returns></returns>
        public static List<Niveau> SelectListeNiveau()
        {
            string strSQL = @"select * from Niveau ";
            using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString))
            {
                cnx.Open();

                SqlCommand cmd = new SqlCommand(strSQL, cnx);


                SqlDataReader dr = cmd.ExecuteReader();

                List<Niveau> List_Niveau = new List<Niveau>();
                while (dr.Read())
                {
                    List_Niveau.Add(ReaderToObject(dr));
                }

                dr.Close();
                cnx.Close();

                return List_Niveau;
            }

        }

    }
}